{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw # 导入模块\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 汇总销售台账"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "汇总=r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\0.商贸分收入确认账务清理\\供应商发货明细\\销售台账汇总\\B0 2023跑腿代理商汇总.xlsx'\n",
    "\n",
    "三月=r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\商贸分收入确认账务清理\\供应商发货明细\\采购流程汇总\\B2 3月份物料统计表.xlsx'\n",
    "四月=r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\商贸分收入确认账务清理\\供应商发货明细\\采购流程汇总\\B3 4月份跑男物料统计表.xlsx'\n",
    "五月=r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\商贸分收入确认账务清理\\供应商发货明细\\采购流程汇总\\B4 5月份代理商物料统计表.xlsx'\n",
    "六月=r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\商贸分收入确认账务清理\\供应商发货明细\\采购流程汇总\\B5 6月份代理商物料统计表.xlsx'\n",
    "七月=r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\商贸分收入确认账务清理\\供应商发货明细\\采购流程汇总\\B6 7月份代理商物料统计表.xlsx'\n",
    "八月=r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\商贸分收入确认账务清理\\供应商发货明细\\采购流程汇总\\B7 8月物料统计表.xlsx'\n",
    "九月=r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\商贸分收入确认账务清理\\供应商发货明细\\采购流程汇总\\B8 9月份物料统计表.xlsx'\n",
    "十二月=r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\商贸分收入确认账务清理\\供应商发货明细\\采购流程汇总\\B9 10-12月份物料统计表.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开汇总表\n",
    "wb汇总=xw.Book(r'B01 2023家政物料销售汇总（家政物料汇总）.xlsx')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开各月的工作表，向汇总表中汇总\n",
    "wb各月=xw.Book(三月)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将指定的sheet移动到总表中\n",
    "\n",
    "sheet_to_move='3.14-3.31'\n",
    "newsheet=wb各月.sheets(sheet_to_move).copy(after=wb汇总.sheets[wb汇总.sheets.count-1])\n",
    "newsheet.name='3月-2'  #每次修改"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 处理合并单元格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "for sheet in wb汇总.sheets:\n",
    "\n",
    "    # 获取列的名称\n",
    "    column = 'a'  #处理A列\n",
    "\n",
    "    # 获取列的范围\n",
    "    column_range = sheet.range(f'{column}1:{column}1000')\n",
    "\n",
    "    # 遍历列中的每个单元格\n",
    "\n",
    "    for cell in column_range:\n",
    "        if cell.api.MergeCells:   #如果是合并单元格\n",
    "            # 获取整个合并单元格区域的范围\n",
    "            merged_range = cell.api.MergeArea.Address\n",
    "            # 解散合并单元格\n",
    "            cell.api.UnMerge()\n",
    "            # 用合并单元格的值填充合并区域内的所有单元格\n",
    "            sheet.range(merged_range).value = cell.value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "for sheet in wb汇总.sheets:\n",
    "\n",
    "    # 获取列的名称\n",
    "    column = 'b'  #处理B列\n",
    "\n",
    "    # 获取列的范围\n",
    "    column_range = sheet.range(f'{column}1:{column}1000')\n",
    "\n",
    "    # 遍历列中的每个单元格\n",
    "\n",
    "    for cell in column_range:\n",
    "        if cell.api.MergeCells:   #如果是合并单元格\n",
    "            # 获取整个合并单元格区域的范围\n",
    "            merged_range = cell.api.MergeArea.Address\n",
    "            # 解散合并单元格\n",
    "            cell.api.UnMerge()\n",
    "            # 用合并单元格的值填充合并区域内的所有单元格\n",
    "            sheet.range(merged_range).value = cell.value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "sheet=wb汇总.sheets('8月')\n",
    "# 获取列的名称\n",
    "column = 'c'  #处理B列\n",
    "\n",
    "# 获取列的范围\n",
    "column_range = sheet.range(f'{column}1:{column}1000')\n",
    "\n",
    "# 遍历列中的每个单元格\n",
    "\n",
    "for cell in column_range:\n",
    "    if cell.api.MergeCells:   #如果是合并单元格\n",
    "        # 获取整个合并单元格区域的范围\n",
    "        merged_range = cell.api.MergeArea.Address\n",
    "        # 解散合并单元格\n",
    "        cell.api.UnMerge()\n",
    "        # 用合并单元格的值填充合并区域内的所有单元格\n",
    "        sheet.range(merged_range).value = cell.value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 销售台账汇总"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "销售台账=pd.read_excel(r'K:\\BaiduSyncdisk\\王振洋资料\\2.商贸分公司资料\\2月商贸分公司资料\\0.商贸分收入确认账务清理\\供应商发货明细\\销售台账汇总\\B01 2023家政物料销售汇总（家政物料汇总）.xlsx',sheet_name=None)  #销售台账是一个字典，键是sheet名字，value是sheet的内容转换成的dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "汇总df=pd.DataFrame() #创建一个空的DataFrame用于合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in wb汇总.sheet_names[0:]:  # 循环遍历销售台账这个字典，每次都取出其中的value（一个df)，然后进行纵向追加汇总，自动对齐列\n",
    "    a=销售台账[i]\n",
    "    a['来源']= i #sheet的名字，注明dataframe来自于哪个工作表\n",
    "    汇总df=pd.concat([汇总df,a])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2月'"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb汇总.sheet_names[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "汇总df=汇总df.loc[:,['来源','日期','城市','物品','数量','单价','总价']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 定义一个函数来转换数字格式的日期为日期时间类型，保持其他值不变\n",
    "def convert_to_datetime(x):\n",
    "    try:\n",
    "        return pd.to_datetime(x,origin='1899-12-30', unit='D',errors='coerce')  #这里的errors='coerce'是什么意思？\n",
    "    except: \n",
    "        return x\n",
    "汇总df['日期']=汇总df['日期'].apply(convert_to_datetime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按城市、物品、日期来进行排序\n",
    "# wb对账.sheets[0].range('a1').value=汇总df.sort_values(by=['城市','物品','日期'])\n",
    "汇总df=汇总df.sort_values(by=['城市','日期','物品'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "汇总df.to_clipboard()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 处理供应商发货明细"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 处理合并单元格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建对账工作簿\n",
    "wb对账=xw.books.add()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 处理合并单元格\n",
    "sheet = wb对账.sheets[0] # 要处理的sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 选择要检查的列\n",
    "column = 'b'\n",
    "\n",
    "# 获取列的范围\n",
    "column_range = sheet.range(f'{column}1:{column}1000')\n",
    "\n",
    "# 遍历列中的每个单元格\n",
    "\n",
    "for cell in column_range:\n",
    "    if cell.api.MergeCells:   #如果是合并单元格\n",
    "        # 获取整个合并单元格区域的范围\n",
    "        merged_range = cell.api.MergeArea.Address\n",
    "        # 解散合并单元格\n",
    "        cell.api.UnMerge()\n",
    "        # 用合并单元格的值填充合并区域内的所有单元格\n",
    "        sheet.range(merged_range).value = cell.value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 发货明细导入到dataframe中进行数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {},
   "outputs": [],
   "source": [
    "发货df=sheet.range('A1').current_region.options(pd.DataFrame,index=False).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [],
   "source": [
    "发货df=发货df.loc[:,['日期','城市','物品','数量','单价','总价']].sort_values(by=['城市','物品','日期'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {},
   "outputs": [],
   "source": [
    "发货df.to_clipboard()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 两个df拼接，数据核对"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 301,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建一个空的dataframe\n",
    "df = pd.DataFrame()\n",
    "df1=pd.DataFrame()\n",
    "发货筛选=发货df\n",
    "\n",
    "for i,j in 汇总df.groupby(by=['城市']):\n",
    "    b=i[0]\n",
    "    c=b.replace(\"县\",'')\n",
    "\n",
    "    汇总=汇总df.query('城市==@b').reset_index(drop=True).sort_values(by=['城市','物品','日期'])\n",
    "    \n",
    "    发货=发货筛选.query('城市.str.contains(@c)',engine='python').reset_index(drop=True).sort_values(by=['城市','物品','日期'])\n",
    "\n",
    "    发货筛选=发货筛选.query('~(城市.str.contains(@c))',engine='python')  #筛选之后把没选上的部分放到一个df里，最后剩下的就是一直没选上的了。\n",
    "\n",
    "    拼接=pd.concat([汇总,发货],axis=1)\n",
    "\n",
    "\n",
    "    df=pd.concat([df,拼接])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 303,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_clipboard()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "发货筛选.to_clipboard()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 294,
   "metadata": {},
   "outputs": [],
   "source": [
    "a=df1.sort_values(by=['城市','物品','日期']).reset_index(drop=True)\n",
    "c=a.groupby(by=['城市'])['数量'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "metadata": {},
   "outputs": [],
   "source": [
    "b=发货df.sort_values(by=['城市','物品','日期']).reset_index(drop=True)\n",
    "d=b.groupby(by=['城市'])['数量'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 297,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "300857.0"
      ]
     },
     "execution_count": 297,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a['总价'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>城市</th>\n",
       "      <th>物品</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>总价</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>云南红河州</td>\n",
       "      <td>冲锋衣（亲民版）</td>\n",
       "      <td>20.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>1320.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>云南红河州</td>\n",
       "      <td>冲锋衣（普通款）</td>\n",
       "      <td>20.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>1240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>云南红河州</td>\n",
       "      <td>冲锋衣（普通款）</td>\n",
       "      <td>20.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>1240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>云南红河州</td>\n",
       "      <td>短袖（新款）</td>\n",
       "      <td>20.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2023-01-03</td>\n",
       "      <td>云南红河州</td>\n",
       "      <td>短袖（新款）</td>\n",
       "      <td>20.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347</th>\n",
       "      <td>2023-03-20</td>\n",
       "      <td>黑山县</td>\n",
       "      <td>冲锋衣（尊享版）</td>\n",
       "      <td>2.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>172.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>348</th>\n",
       "      <td>2023-03-20</td>\n",
       "      <td>黑山县</td>\n",
       "      <td>短袖</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>349</th>\n",
       "      <td>2023-03-20</td>\n",
       "      <td>黑山县</td>\n",
       "      <td>雨衣（分体）</td>\n",
       "      <td>2.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>122.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>2023-05-22</td>\n",
       "      <td>黔西</td>\n",
       "      <td>保温箱（普通版）加大</td>\n",
       "      <td>5.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>270.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>2023-05-22</td>\n",
       "      <td>黔西</td>\n",
       "      <td>冲锋衣（尊享版）</td>\n",
       "      <td>5.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>430.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>346 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期     城市          物品    数量    单价      总价\n",
       "6   2023-01-03  云南红河州    冲锋衣（亲民版）  20.0  66.0  1320.0\n",
       "7   2023-01-03  云南红河州    冲锋衣（普通款）  20.0  62.0  1240.0\n",
       "8   2023-01-03  云南红河州    冲锋衣（普通款）  20.0  62.0  1240.0\n",
       "9   2023-01-03  云南红河州      短袖（新款）  20.0  25.0   500.0\n",
       "10  2023-01-03  云南红河州      短袖（新款）  20.0  25.0   500.0\n",
       "..         ...    ...         ...   ...   ...     ...\n",
       "347 2023-03-20    黑山县    冲锋衣（尊享版）   2.0  86.0   172.0\n",
       "348 2023-03-20    黑山县          短袖   1.0  25.0    25.0\n",
       "349 2023-03-20    黑山县      雨衣（分体）   2.0  61.0   122.0\n",
       "350 2023-05-22     黔西  保温箱（普通版）加大   5.0  54.0   270.0\n",
       "351 2023-05-22     黔西    冲锋衣（尊享版）   5.0  86.0   430.0\n",
       "\n",
       "[346 rows x 6 columns]"
      ]
     },
     "execution_count": 283,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diff_rows_data1"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
